The NULLCHAR and NULLCHARVAL data set options are not honored when you are using the SAS/ACCESS^® Interface to Netezza bulk-load facility and you write data from SAS to Netezza.

The following code illustrates this behavior:

proc sql;
create table work.foo (i int, c char(10));
insert into work.foo values (null, null);
quit;

libname x netezza server=nzserver user=nzuser pwd=nzpwd database=nzdb;

proc sql;
create table x.foo (i int, c char(10));
quit;

proc append base=x.foo (bulkload=yes)data=work.foo;
run;

SAS tracing shows that the bulk-load facility generates a query like this:

INSERT INTO TEST..foo SELECT * FROM  EXTERNAL
'\\.\pipe\BL_foo_B9C24C51-0C90-4C29-BDA5-B0F828E0A59F' USING ( DELIMITER '|'
REMOTESOURCE 'ODBC'
NULLVALUE '' )

The generated query specifies NULLVALUE ' ', so any source nulls are represented as NULL in Netezza.

If you want a NULLVALUE of X, submit code specifying NULLCHARVAL='x'. Here is an example:

/* try with NULLCHAR=NO and NULLCHARVAL='x' options */
/* you expect that instead of NULL, you get 'x' */

proc append base=x.foo
(bulkload=yes nullchar=no nullcharval='x')
data=work.foo;
run;

But SASTRACE shows the same generated query:

INSERT INTO TEST..foo SELECT * FROM  EXTERNAL
'\\.\pipe\BL_foo_5DB60B77-E56D-4451-812E-11463C52E203' USING ( DELIMITER '|'
REMOTESOURCE 'ODBC'
NULLVALUE '' )

This behavior does not occur in DATA step code that does not specify BULKLOAD.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for SAS/ACCESS Interface to Netezza 9.3_M1 is available at:

A fix for this issue for SAS/ACCESS Interface to Netezza 9.4_M3 is available at: